package com.swordget.dao.impl;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.swordget.commons.JdbcUtil;
import com.swordget.dao.DataHandlerDao;
import com.swordget.entity.ColumnDesign;
import com.swordget.entity.po.CheckLogPo;
import com.swordget.entity.po.DataQueryPo;
import com.swordget.service.SwitchingDataSourcesService;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;

import java.io.File;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.*;
import java.util.*;

import static com.swordget.commons.ColumnType.NUMBER;
import static com.swordget.commons.ColumnType.VARCHAR2;

/**
 * @Author: David
 * @Date: Created in 16:43 2020/4/8
 * @Description: 查询服务 dao 层实现
 * @Modified By:
 * @Version:
 */
public class DataHandlerOracleDaoImp implements DataHandlerDao {

    @Autowired
    private DruidDataSource dataSource;

    @Autowired
    private JdbcUtil jdbcUtil;


    @Autowired
    private SwitchingDataSourcesService switchingDataSourcesService;

    private int pageSize = 5000;
    /**
     * DataHandlerOracleDaoImp log
     */
    private final static Logger LOG = LoggerFactory.getLogger(DataHandlerOracleDaoImp.class);

    @Override
    public List<Map<String, Object>> query(String tableName, List<String> columns, List<String> types) {
        //组装sql
        String querySql = String.format("SELECT %s FROM %s", String.join(",", columns), tableName);
        return this.executeQuery(querySql, columns, types, null, "");
    }


    @Override
    public DataQueryPo query(int rows, int page, JSONArray params, String tableName, List<String> columns,
                             List<String> types, List<ColumnDesign> columnDesigns, String dataSourceId) {
        //查询数据
        final String queryColumn = String.join(",", columns);
        final String paramSql = this.handlerParams(params);
        String querySql = String.format("SELECT * FROM (" +
                        "SELECT %s, ROWNUM AS rowno FROM (" +
                        "SELECT %s FROM %s WHERE %s ORDER BY CREATEDATE, CREATETIME DESC) t WHERE ROWNUM <= %d" +
                        ") WHERE rowno >= %d", queryColumn, queryColumn, tableName, paramSql,
                (page * rows), (page - 1) * rows + 1);
        String countSql = String.format("SELECT COUNT(*) FROM %s WHERE %s", tableName, paramSql);
        return DataQueryPo.builder()
                .data(this.executeQuery(querySql, columns, types, columnDesigns, dataSourceId))
                .total(jdbcUtil.executeCount(countSql))
                .build();
    }


    @Override
    public List<List<String>> queryByList(int rows, int page, JSONArray queryParam, String tableName, List<String> columns, List<String> types) {
        //查询数据
        final String queryColumn = String.join(",", columns);
        final String paramSql = this.handlerParams(queryParam);
        String querySql = String.format("SELECT * FROM (" +
                        "SELECT %s, ROWNUM AS rowno FROM (" +
                        "SELECT %s FROM %s WHERE %s ORDER BY CREATEDATE, CREATETIME, ROWNUM DESC) t WHERE ROWNUM <= %d" +
                        ") WHERE rowno >= %d", queryColumn, queryColumn, tableName, paramSql,
                (page * rows), (page - 1) * rows);
        return this.executeQueryByListTemplate(querySql, columns, types);
    }

    @Override
    public List<List<String>> queryByListByStatus(int rows, int page, JSONArray queryParam, String tableName,
                                                  List<String> columns, List<String> types, List<ColumnDesign> columnDesigns,
                                                  String[] serials, String dataSourceId) {
        //查询数据
        final String queryColumn = String.join(",", columns);
        final String paramSql = this.handlerParams(queryParam, serials);
        String querySql = String.format("SELECT * FROM (" +
                        "SELECT %s, ROWNUM AS rowno FROM (" +
                        "SELECT %s FROM %s WHERE %s ORDER BY CREATEDATE, CREATETIME, ROWNUM DESC) t WHERE ROWNUM <= %d" +
                        ") WHERE rowno > %d", queryColumn, queryColumn, tableName, paramSql,
                (page * rows), (page - 1) * rows);
        return this.executeQueryByList(querySql, columns, types, columnDesigns, dataSourceId);
    }


    @Override
    public int insert(File loadData, String tableName, List<String> columns) throws SQLException, Exception {
        return 0;
    }

    @Override
    public boolean copyData(String tableName, String tableNameTmp, List<String> columns, String importId) {
        return false;
    }

    @Override
    public boolean removeByImportId(String tableNameTmp, String importId) {
        String deleteCommand = String.format("DELETE FROM %s WHERE IMPORTID = '%s'", tableNameTmp, importId);
        return jdbcUtil.execute(deleteCommand);
    }

    @Override
    public boolean addCheckLog(CheckLogPo checkLogPo) {
        String command = "INSERT INTO CHECKLOG(SERIALNO,TABLEID,TABLECOMMENT,CREATEUSER,MAKEUSER,MAKESTATUS,CREATEDATE," +
                "CREATETIME,MAKEDATE,MAKETIME) VALUES(" + checkLogPo.toString() + ")";
        return jdbcUtil.execute(command);
    }

    @Override
    public boolean applyRemove(String tableName, String idS, String handleDate, String handleTime, String handleUser, String handleName, String status) {
        String command = String.format("UPDATE %s SET HANDLEDATE = '%s', HANDLETIME = '%s', HANDLEUSER = '%s', " +
                        "HANDLENAME = '%s', STATUS = '%s' WHERE SERIALNO IN (%s)",
                tableName, handleDate, handleTime, handleUser, handleName, status, idS);
        return jdbcUtil.execute(command);
    }

    @Override
    public boolean dataModify(String tableName, JSONArray searchParams, String handleDate, String handleTime, String handleUser, String handleName, String status) {
        //处理参数
        String params = handlerParams(searchParams);
        String command = String.format("UPDATE %s SET HANDLEDATE = '%s', HANDLETIME = '%s', HANDLEUSER = '%s', " +
                        "HANDLENAME = '%s', STATUS = '%s' WHERE %s",
                tableName, handleDate, handleTime, handleUser, handleName, status, params);
        return jdbcUtil.execute(command);
    }

    @Override
    public boolean dataModify(String tableName, JSONArray searchParams, String handleDate, String handleTime, String handleUser, String handleName, String notPassMark, String status) {
        //处理参数
        String params = handlerParams(searchParams);
        String command = String.format("UPDATE %s SET HANDLEDATE = '%s', HANDLETIME = '%s', HANDLEUSER = '%s', " +
                        "HANDLENAME = '%s', FAILREASON = '%s', STATUS = '%s' WHERE %s",
                tableName, handleDate, handleTime, handleUser, handleName, notPassMark, status, params);
        return jdbcUtil.execute(command);
    }

    @Override
    public boolean pass(String tableName, JSONArray searchParams, String handleDate, String handleTime, String handleUser) {
        //处理参数
        String params = handlerParams(searchParams);
        //先更新操作时间操作人等字段
        String command = String.format("UPDATE %s SET HANDLEDATE = '%s', HANDLETIME = '%s', HANDLEUSER = '%s' WHERE %s",
                tableName, handleDate, handleTime, handleUser, params);
        //复制数据到正式表
        String insertCommand = String.format("INSERT INTO %s SELECT * FROM %s WHERE %s", tableName, tableName, params);

        //删除临时表中数据
        String deleteCommand = String.format("DELETE FROM %s WHERE %s", tableName, params);

        //事务执行
        return jdbcUtil.executeBatch(command, insertCommand, deleteCommand);
    }

    @Override
    public boolean applyDataModify(String tableName, JSONArray searchParams, String handleDate, String handleTime, String handleUser, String handleName, String status) {
        //处理参数
        String params = handlerParams(searchParams);
        String command = String.format("UPDATE %s SET HANDLEDATE = '%s', HANDLETIME = '%s', HANDLEUSER = '%s', " +
                        "HANDLENAME = '%s', STATUS = '%s' WHERE %s",
                tableName, handleDate, handleTime, handleUser, handleName, status, params);
        return jdbcUtil.execute(command);

    }

    @Override
    public boolean passDataModify(String tableName, JSONArray searchParams, String handleDate, String handleTime, String handleUser, String handleName, String status) {
        //处理参数
        String params = handlerParams(searchParams);

        //复制数据到正式表
        String insertCommand = String.format("INSERT INTO %s SELECT * FROM %s WHERE %s", tableName, tableName, params);

        //先更新操作时间操作人等字段
        String command = String.format("UPDATE %s SET HANDLEDATE = '%s', HANDLETIME = '%s', HANDLEUSER = '%s', " +
                        "HANDLENAME = '%s', STATUS = '%s' WHERE %s",
                tableName, handleDate, handleTime, handleUser, handleName, status, params);

        //删除临时表中数据
        String deleteCommand = String.format("DELETE FROM %s WHERE %s", tableName, params);

        //事务执行
        return jdbcUtil.executeBatch(insertCommand, command, deleteCommand);

    }

    @Override
    public Integer checkNullOrContainsHanleName(String tableName, JSONArray searchParams) {
        int count = getTotal(tableName, searchParams, null, "");
        if (count == 0) {
            //未查到数据
            return 0;
        }
        int step = count / pageSize + 1;
        List<String> columns = Collections.unmodifiableList(Arrays.asList(new String[]{"HANDLENAME"}));
        List<String> types = Collections.unmodifiableList(Arrays.asList(new String[]{"VARCHAR"}));
        for (int i = 1; i <= step; i++) {
            DataQueryPo dataQueryPo = query(pageSize, i, searchParams, tableName, columns, types, null, "");
            for (Map<String, Object> data : dataQueryPo.getData()) {
                if (data.get("HANDLENAME") == null || StringUtils.isBlank(data.get("HANDLENAME").toString())) {
                    return 1;
                }
            }
        }
        return 2;

    }

    @Override
    public int getTotal(String tableName, JSONArray queryParams, String[] serials, String dataSourceId) {
        final String paramSql = this.handlerParams(queryParams, serials);
        String countSql = String.format("SELECT COUNT(*) FROM %s WHERE %s", tableName, paramSql);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = 0;
        try {
            conn = switchingDataSourcesService.switchingDataSources(dataSourceId);
            ps = conn.prepareStatement(countSql);
            rs = ps.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }

                if (ps != null) {
                    ps.close();
                }

                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return count;
    }

    @Override
    public boolean agreeDataModify(String tableName, JSONArray searchParams, String handleDate, String handleTime, String handleUser, String handleName, String status, String lastStatus) {
        //处理参数
        String params = handlerParamsQuery(searchParams);
        String command = String.format("UPDATE %s SET HANDLEDATE = '%s', HANDLETIME = '%s', HANDLEUSER = '%s', " +
                        "HANDLENAME = '%s', STATUS = '%s'  WHERE %s",
                tableName, handleDate, handleTime, handleUser, handleName, status, params);
        return jdbcUtil.execute(command);

    }

    @Override
    public boolean pass(String tableName, JSONArray searchParams, String handleDate, String handleTime, String handleUser, String status, String lastStatus, String failReason) {
        //处理参数
        String params = handlerParams(searchParams, null);

        //先复制数据到正式表
        String insertCommand = String.format("INSERT INTO %s SELECT * FROM %s WHERE %s", tableName, tableName, params);

        //再更新操作时间操作人等字段
        String command = String.format("UPDATE %s SET HANDLEDATE = '%s', HANDLETIME = '%s', HANDLEUSER = '%s', status = '%s', failReason = '%s' WHERE %s",
                tableName, handleDate, handleTime, handleUser, status, failReason, params);

        //删除临时表中数据
        String deleteCommand = String.format("DELETE FROM %s WHERE %s", tableName, params);

        //事务执行
        return jdbcUtil.executeBatch(insertCommand, command, deleteCommand);

    }


    @Override
    public Integer checkNullOrNotContainsHanleName(String tableName, JSONArray searchParams) {
        int count = getTotal(tableName, searchParams, null, "");
        if (count == 0) {
            //未查到数据
            return 0;
        }
        int step = count / pageSize + 1;
        List<String> columns = Collections.unmodifiableList(Arrays.asList(new String[]{"HANDLENAME"}));
        List<String> types = Collections.unmodifiableList(Arrays.asList(new String[]{"VARCHAR"}));
        for (int i = 1; i <= step; i++) {
            DataQueryPo dataQueryPo = query(pageSize, i, searchParams, tableName, columns, types, null, "");
            for (Map<String, Object> data : dataQueryPo.getData()) {
                if (data.get("HANDLENAME") != null && StringUtils.isNotBlank(data.get("HANDLENAME").toString())) {
                    return 1;
                }
            }

        }
        return 2;

    }


    @Override
    public boolean insert(String tableName, String columns, String values, String dataSourceId) {
        String insertCommand = String.format("INSERT INTO %s (%s) VALUES(%s)", tableName,
                columns, values);

        Connection conn = null;
        PreparedStatement ps = null;
        int row = 0;
        try {
            conn = switchingDataSourcesService.switchingDataSources(dataSourceId);
            ps = conn.prepareStatement(insertCommand);
            row = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return row > 0;
    }

    @Override
    public boolean insert(String tableName, List<String> columns, String[][] datas) {
        final String column = String.join(",", columns);
        Connection conn = null;
        Statement statement = null;
        try {
            conn = dataSource.getConnection();
            statement = conn.createStatement();
            conn.setAutoCommit(false);
            for (String[] data : datas) {
                String values = String.join(",", data);
                StringBuilder commandBuilder = new StringBuilder();
                commandBuilder.append("INSERT INTO ");
                commandBuilder.append(tableName);
                commandBuilder.append(" (");
                commandBuilder.append(column);
                commandBuilder.append(") VALUES(");
                commandBuilder.append(values);
                commandBuilder.append(")");
                statement.execute(commandBuilder.toString());
                //statement.addBatch(commandBuilder.toString());
            }
            conn.commit();
            return true;
        } catch (SQLException e) {
            if (null != conn) {
                try {
                    conn.rollback();
                } catch (SQLException e1) {
                    LOG.error(e.getMessage());

                }
            }
        } finally {
            if (null != statement) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    LOG.error("程序执行过程中出现异常", e);
                }
            }
            if (null != conn) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    LOG.error("程序执行过程中出现异常", e);
                }
            }
        }
        return false;
    }

    @Override
    public boolean modify(String tableName, List<String> names, List<String> values, String keyValue, String dataSourceId) {
        StringBuilder command = new StringBuilder();
        int lastIndex = names.size() - 1;
        command.append("UPDATE ");
        command.append(tableName);
        command.append(" SET ");
        for (int i = 0; i < names.size(); i++) {
            command.append(names.get(i));
            command.append(" = ");
            command.append(values.get(i));
            if (i != lastIndex) {
                command.append(",");
            }
        }
        command.append(" WHERE SERIALNO = '");
        command.append(keyValue);
        command.append("'");

        Connection conn = null;
        PreparedStatement ps = null;
        int row = 0;
        try {
            conn = switchingDataSourcesService.switchingDataSources(dataSourceId);
            ps = conn.prepareStatement(command.toString());
            row = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return row > 0;
    }

    @Override
    public boolean remove(String tableName, List<String> params) {
        String command = String.format("DELETE FROM %s WHERE SERIALNO IN (%s)", tableName, String.join(",", params));
        return jdbcUtil.executeUpdate(command);
    }

    @Override
    public boolean remove(String tableName, JSONArray params, String dataSourceId) {
        String param = handlerParams(params);
        String command = String.format("DELETE FROM %s WHERE %s", tableName, param);
        Connection conn = null;
        PreparedStatement ps = null;
        int row = 0;
        try {
            conn = switchingDataSourcesService.switchingDataSources(dataSourceId);
            ps = conn.prepareStatement(command.toString());
            row = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return row > 0;
    }

    @Override
    public int getTotal(String tableName, JSONArray queryParams) {
        final String paramSql = this.handlerParams(queryParams);
        String countSql = String.format("SELECT COUNT(*) FROM %s WHERE %s", tableName, paramSql);
        return jdbcUtil.executeCount(countSql);
    }

    @Override
    public int getCount(String tableName, String name, String value) {
        String countSql = String.format("SELECT COUNT(*) FROM %s WHERE " + name + " = '%s'", tableName, value);
        return jdbcUtil.executeCount(countSql);
    }

    @Override
    public String getSerialno(String tableName, String name, String value) {
        String countSql = String.format("SELECT SERIALNO FROM %s WHERE %s = '%s'", tableName, name, value);
        return (String) jdbcUtil.findOne(countSql);
    }

    /**
     * 处理查询参数
     *
     * @param params 查询参数对象
     * @return
     */
    protected String handlerParams(JSONArray params) {
        StringBuilder paramBuilder = new StringBuilder();
        paramBuilder.append("1 = 1 ");
        //处理查询参数
        if (params != null) {
            for (int i = 0; i < params.size(); i++) {
                JSONObject jsonObject = params.getJSONObject(i);
                String name = jsonObject.getString("name");
                String value = jsonObject.getString("value");

                if (StringUtils.isBlank(value)) {
                    continue;
                }
                if (value.contains("%")) {
                    value = value.replace("%", "sdkfjslfa");
                }
                if (value.contains("_")) {
                    value = value.replace("_", "sdkfjslfb");
                }

                if (value.startsWith("[") && value.endsWith("]")) {
                    value = value.replaceAll("\\[", "").replaceAll("]", "");
                    paramBuilder.append("AND ");
                    paramBuilder.append(name);
                    paramBuilder.append(" IN(");
                    if (value.contains("'")) {
                        paramBuilder.append(value);
                    } else {
                        String[] arr = value.split(",");
                        String values = "";
                        for (int j = 0; j < arr.length; j++) {
                            values += ",'" + arr[j] + "'";
                        }
                        paramBuilder.append(values.substring(1));
                    }
                    paramBuilder.append(") ");
                } else {
                    paramBuilder.append("AND ");
                    paramBuilder.append(name);
                    paramBuilder.append(" like '%");
                    paramBuilder.append(value);
                    paramBuilder.append("%' ");
                }

            }
        }

        return paramBuilder.toString();
    }

    /**
     * 处理查询参数
     *
     * @param params 查询参数对象
     * @return
     */
    protected String handlerParamsQuery(JSONArray params) {
        StringBuilder paramBuilder = new StringBuilder();
        paramBuilder.append("1 = 1 ");
        //处理查询参数
        if (params != null) {
            for (int i = 0; i < params.size(); i++) {
                JSONObject jsonObject = params.getJSONObject(i);
                String name = jsonObject.getString("name");
                String value = jsonObject.getString("value");

                if (StringUtils.isBlank(value) || "status".equals(name)) {
                    continue;
                }
                if (value.startsWith("[") && value.endsWith("]")) {
                    value = value.replaceAll("\\[", "").replaceAll("]", "");
                    paramBuilder.append("AND ");
                    paramBuilder.append(name);
                    paramBuilder.append(" IN(");
                    if (value.contains("'")) {
                        paramBuilder.append(value);
                    } else {
                        String[] arr = value.split(",");
                        String values = "";
                        for (int j = 0; j < arr.length; j++) {
                            values += ",'" + arr[j] + "'";
                        }
                        paramBuilder.append(values.substring(1));
                    }
                    paramBuilder.append(") ");
                } else {
                    paramBuilder.append("AND ");
                    paramBuilder.append(name);
                    paramBuilder.append(" = '");
                    paramBuilder.append(value);
                    paramBuilder.append("' ");
                }

            }
        }
        return paramBuilder.toString();
    }


    /**
     * 具体查询实现
     *
     * @param command 执行的sql语句
     * @param columns 字段集合
     * @param types   类型集合
     * @return
     */
    protected List<Map<String, Object>> executeQuery(String command, List<String> columns,
                                                     List<String> types, List<ColumnDesign> columnDesigns, String dataSourceId) {
        List<Map<String, Object>> resultList = new ArrayList<>();
        try (Connection conn = switchingDataSourcesService.switchingDataSources(dataSourceId);
             Statement statement = conn.createStatement();
             ResultSet resultSet = statement.executeQuery(command)) {
            LOG.info(command);
            final int cellNum = types.size();
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<>(cellNum);
                for (int i = 0, j = 1; i < cellNum; i++, j++) {
                    if (VARCHAR2.equals(types.get(i))) {
                        map.put(columns.get(i), resultSet.getString(j));
                    } else if (NUMBER.equals(types.get(i))) {
                        // 根据表设计格式化number小数位
                        BigDecimal value = resultSet.getBigDecimal(j);
                        if (Objects.nonNull(columnDesigns) && columnDesigns.get(i).getPoint() != 0) {
                            if (Objects.nonNull(value)) {
                                value = value.setScale(columnDesigns.get(i).getPoint(), RoundingMode.HALF_DOWN);
                            }
                        }
                        map.put(columns.get(i), Objects.nonNull(value) ? value.toString() : "");
                    } else {
                        map.put(columns.get(i), null);
                    }
                }
                resultList.add(map);
            }
        } catch (SQLException e) {
            LOG.error("程序执行过程中出现异常", e);
        }
        return resultList;
    }

    protected List<List<String>> executeQueryByList(String command, List<String> columns, List<String> types,
                                                    List<ColumnDesign> columnDesigns, String dataSourceId) {
        List<List<String>> resultList = new ArrayList<>();
        try (Connection conn = switchingDataSourcesService.switchingDataSources(dataSourceId);
             Statement statement = conn.createStatement();
             ResultSet resultSet = statement.executeQuery(command)) {
            LOG.info(command);
            final int cellNum = columns.size();
            while (resultSet.next()) {
                List<String> list = new ArrayList<>(cellNum);
                for (int i = 0, j = 1; i < cellNum; i++, j++) {
                    if (NUMBER.equals(types.get(i))) {
                        BigDecimal value = resultSet.getBigDecimal(j);
                        if (Objects.nonNull(columnDesigns) && columnDesigns.get(i).getPoint() != 0) {
                            if (Objects.nonNull(value)) {
                                value = value.setScale(columnDesigns.get(i).getPoint(), RoundingMode.HALF_DOWN);
                            }
                        }
                        list.add(Objects.nonNull(value) ? value.toString() : "");
                    } else {
                        list.add(resultSet.getString(j));
                    }

                }
                resultList.add(list);
            }
        } catch (SQLException e) {
            LOG.error("程序执行过程中出现异常", e);
        }
        return resultList;
    }


    protected List<List<String>> executeQueryByListTemplate(String command, List<String> columns, List<String> types) {
        List<List<String>> resultList = new ArrayList<>();
        try (Connection conn = dataSource.getConnection();
             Statement statement = conn.createStatement();
             ResultSet resultSet = statement.executeQuery(command)) {
            LOG.info(command);
            final int cellNum = columns.size();
            while (resultSet.next()) {
                List<String> list = new ArrayList<>(cellNum);
                for (int i = 0, j = 1; i < cellNum; i++, j++) {
                    list.add(resultSet.getString(j));
                }
                resultList.add(list);
            }
        } catch (SQLException e) {
            LOG.error("程序执行过程中出现异常", e);
        }
        return resultList;
    }

    /**
     * 处理查询参数
     *
     * @param params 查询参数对象
     * @return
     */
    protected String handlerParams(JSONArray params, String[] serials) {
        StringBuilder paramBuilder = new StringBuilder();
        paramBuilder.append("1 = 1 ");
        //处理查询参数
        if (params != null) {
            for (int i = 0; i < params.size(); i++) {
                JSONObject jsonObject = params.getJSONObject(i);
                String name = jsonObject.getString("name");
                String value = jsonObject.getString("value");

                if (StringUtils.isBlank(value)) {
                    continue;
                }
                if (value.contains("%")) {
                    value = value.replace("%", "sdkfjslfa");
                }
                if (value.contains("_")) {
                    value = value.replace("_", "sdkfjslfb");
                }
                if (value.startsWith("[") && value.endsWith("]")) {
                    value = value.replaceAll("\\[", "").replaceAll("]", "");
                    paramBuilder.append("AND ");
                    paramBuilder.append(name);
                    paramBuilder.append(" IN(");
                    if (value.contains("'")) {
                        paramBuilder.append(value);
                    } else {
                        String[] arr = value.split(",");
                        String values = "";
                        for (int j = 0; j < arr.length; j++) {
                            values += ",'" + arr[j] + "'";
                        }
                        paramBuilder.append(values.substring(1));
                    }
                    paramBuilder.append(") ");
                } else {
                    paramBuilder.append("AND ");
                    paramBuilder.append(name);
                    paramBuilder.append(" like '%");
                    paramBuilder.append(value);
                    paramBuilder.append("%' ");
                }

            }
        }
        if (serials != null && serials.length > 0 && !serials[0].equals("")) {
            String ser = "";
            for (String serial : serials) {
                ser = ser + "'" + serial + "',";
            }
            ser = ser.substring(0, ser.length() - 1);
            paramBuilder.append(" AND SERIALNO IN (");
            paramBuilder.append(ser);
            paramBuilder.append(") ");
        }
        return paramBuilder.toString();
    }


}
